package com.api.shopapi.mapper;

import com.api.shopapi.entity.ShopPageData;
import com.api.shopapi.entity.dto.ElderlyViewsDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
public interface ViewsMapper {


    //现有服务对象
    @Select("<script>" +
            "SELECT count(ElderlyCode)       'total', " +
            "       ifnull(SUM(case when e.Gender = 1 then 1 else 0 end),0)       'man', " +
            "       ifnull(SUM(case when e.Gender = 0 then 1 else 0 end),0)       'woman' " +
            "FROM elderly as e " +
            "         left join user as u on e.UserCode = u.UserCode " +
            "WHERE  " +
            "   u.Status = 1 " +
            "  and u.GovernmentCode = #{governmentCode}  " +
            "</script>"
    )
    ShopPageData getElderly(@Param("governmentCode") String governmentCode);

    //本月新增服务对象
    @Select("SELECT count(ElderlyCode) total,ifnull(SUM(case when e.Gender = 1 then 1 else 0 end),0)  man,ifnull(SUM(case when e.Gender = 0 then 1 else 0 end),0) woman FROM (SELECT UserCode,ElderlyCode,Gender from elderly WHERE DATE_FORMAT(CreateTime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )) as e  left join user as u on e.UserCode = u.UserCode  WHERE   u.Status = 1  and u.GovernmentCode = #{governmentCode}"
    )
    ShopPageData getElderlyByNew(@Param("governmentCode") String governmentCode);

    //累计服务对象
    @Select("SELECT count(ElderlyCode) total,ifnull(SUM(case when e.Gender = 1 then 1 else 0 end),0)  man,ifnull(SUM(case when e.Gender = 0 then 1 else 0 end),0) woman FROM elderly as e  left join user as u on e.UserCode = u.UserCode  WHERE u.GovernmentCode = #{governmentCode}"
    )
    ShopPageData getElderlyTotal(@Param("governmentCode") String governmentCode);


    //获取一个政府下服务对象数量
    @Select("select count(e.ElderlyCode) from elderly as e left join `order` u on e.UserCode=u.UserCode " +
            "where u.Status=1 and u.GovernmentCode = #{governmentCode}")
    int getElderlyCount(@Param("governmentCode") String governmentCode);



    //获取一个政府下订单总数量
    @Select("<script>" +
            "select count(ob.OrderBusinessCode) from order_business as ob left join `order` o on o.OrderCode=ob.OrderCode " +
            "where o.GovernmentCode=#{governmentCode}" +
            " and ob.Status in (14, 16,3,7) " +
            "</script>")
    int getGovernmentOrderCount(@Param("governmentCode") String governmentCode);

    //获取一个政府下单量
    @Select("<script>" +
            "select count(ob.OrderBusinessCode) from order_business as ob left join `order` o on o.OrderCode=ob.OrderCode " +
            "where o.GovernmentCode=#{governmentCode}" +
            " and ob.Status in (11,0,10) " +
            "</script>")
    int getTotalBuildNew(@Param("governmentCode") String governmentCode);

    //获取一个政府的接单量
    @Select("<script>" +
            "select count(ob.OrderBusinessCode) from order_business as ob left join `order` o on o.OrderCode=ob.OrderCode " +
            "where o.GovernmentCode=#{governmentCode}" +
            " and ob.Status in (1,12) " +
            "</script>")
    int getTotalAcceptNew(@Param("governmentCode") String governmentCode);

//    获取一个政府正在服务的工单量
@Select("<script>" +
        "select count(ob.OrderBusinessCode) from order_business as ob left join `order` o on o.OrderCode=ob.OrderCode " +
        "where o.GovernmentCode=#{governmentCode}" +
        " and ob.Status in (2,13) " +
        "</script>")
    int getTotalServeNew(@Param("governmentCode") String governmentCode);

//      获取服务的老人的总人数
    @Select("<script>" +
            "select count(*) from elderly where GovernmentCode=#{governmentCode}"+
            "</script>")
    int getTotalServiceObjectNew(@Param("governmentCode") String governmentCode);

    //查询服务商数量
    @Select("select count(BusinessCode) from business where GovernmentCode=#{governmentCode}")
    int getTotalBusinessNew(@Param("governmentCode") String governmentCode);

    //查询服务人员数量
    @Select("select count(serveCode) from serve as s,`user` u where s.userCode=u.userCode and u.UserType=4 " +
            "and u.GovernmentCode=#{governmentCode}")
    int getTotalServicePeopleNew(@Param("governmentCode") String governmentCode);


    //查询本月新增多少女性的服务对象
    @Select("SELECT count(*) FROM elderly WHERE gender = 0  and governmentCode=#{governmentCode} " +
            "and date_format(CreateTime,'%Y-%m') = date_format(now(),'%Y-%m')")
    int getWomanService(@Param("governmentCode") String governmentCode);

    //查询本月新增多少男性的服务对象
    @Select("select count(*) from elderly where gender = 1 and governmentCode=#{governmentCode} " +
            "and date_format(CreateTime,'%Y-%m') = date_format(now(),'%Y-%m')")
    int getManService(@Param("governmentCode") String governmentCode);

    //本月新增多少新的服务对象
    @Select("SELECT count(*) FROM elderly WHERE governmentCode=#{governmentCode}\n" +
            "and date_format(CreateTime,'%Y-%m') = date_format(now(),'%Y-%m')")
    int getTotalServiceNews(@Param("governmentCode") String governmentCode);

    //查询当前70岁以上的老人有低保的数量
    @Select("SELECT count(ec.ElderlyClassCode)\n" +
            "FROM elderly_class as ec , elderly as e\n" +
            "WHERE  ec.value = 0 and e.CreateTime between date_sub(now(),interval 6 month) and e.GovernmentCode = ec.GovernmentCode\n" +
            "and e.GovernmentCode = #{governmentCode}")
    int getClassOneNews(@Param("governmentCode") String governmentCode);

    //查询当前70岁以上的空巢老人的数量
    @Select("SELECT count(*)\n" +
            "FROM elderly\n" +
            "WHERE TIMESTAMPDIFF(YEAR,birthday,CURDATE())>='70' " +
            "and date_format(CreateTime,'%Y-%m') = date_format(now(),'%Y-%m') " +
            "and Children=0 and GovernmentCode =#{governmentCode}")
    int getClassTwoNews(@Param("governmentCode") String governmentCode);

    //查询当前老人年龄超过90岁的
    @Select("SELECT count(*)\n" +
            "FROM elderly\n" +
            "WHERE TIMESTAMPDIFF(YEAR,birthday,CURDATE())>='90' and date_format(CreateTime,'%Y-%m') = date_format(now(),'%Y-%m') " +
            "and GovernmentCode =#{governmentCode}")
    int getClassThreeNews(@Param("governmentCode")String governmentCode);

    //服务对象
    @Select("<script>" +
            "select Name,Phone,ZoneName,IdCard,Birthday,HomeAddress,IsMarriage,Lat,Lon,Gender,ElderlyCode from  elderly where Status=1 and Lat is not null and Lon is not null  and GovernmentCode  in (" +
            "<foreach collection='governmentCodeList' item='item' separator=','>" +
            "#{item}" +
            "</foreach>" +
            ")"+
            "</script>")
    List<ElderlyViewsDTO> getList(@Param("governmentCodeList")List<String> governmentCodeList);
}
